﻿using Meiam.System.Interfaces.IService;
using Meiam.System.Model.Entity;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Meiam.System.Interfaces.Service
{
    public class CutPieceTotalNumService : SqlSugarBase<CutPieceTotalNum>, ICutPieceTotalNumService
    {
        public CutPieceTotalNumService(IConfiguration _configuration)
        {
            base.connectionString = _configuration.GetConnectionString("DB1");
        }

        #region 切片刀数断线推送
        public List<CutPieceTotalNum> GetSummaryData12H()
        {
            DateTime dtNow = DateTime.Now;
            DateTime dtNowPrev = dtNow.AddHours(-12);
            string startTime = dtNowPrev.ToShortDateString() + " " + (dtNowPrev.Hour > 9 ? dtNowPrev.Hour.ToString() : (string.Format("{0}{1}", "0", dtNowPrev.Hour))) + ":00:00";
            string endTime = dtNow.ToShortDateString() + " " + (dtNow.Hour > 9 ? dtNow.Hour.ToString() : (string.Format("{0}{1}", "0", dtNow.Hour))) + ":00:00";

            string sql = @"select tb.WORKSHOP,tb.KJDS,tb.XJDS,tb.DXDS,ROUND(tb.DXDS*100/tb.KJDS,0)||'%' as DXRATE,tb.SBSL,ROUND(tb.KJDS/tb.SBSL,1) AS DJDS  from
(
with tab as(
select DGW.WORKSHOP||t1.spec as WorkShop,to_char(t1.createtime,'yyyy-mm-dd hh24:mi:ss') stime,t1.createtime,t1.JBNUMBER,t1.OPRATION,t1.DESCRIPTION,t1.machineno,rank()over(partition by t1.machineno order by t1.createtime) rk from (
 select * from (
    select row_number() over (partition by c.containername order by hml1.systemdate) as rno, c.containername as JBNUMBER,substr(c.containername,6,3) as spec,hml1.systemdate AS createtime,
    hml1.resourcename as machineno,EM.DESCRIPTION as DESCRIPTION,em.employeename as OPRATION 
 from Container c 
left join historymainline hml1 on c.containername = hml1.containername and hml1.specname = N'切片' and hml1.cdoname = 'TrackInLot'
left join MESSCADA.data_shift t3 on to_char(hml1.systemdate,'YYYYMMdd') = to_char(t3.shift_time,'YYYYMMdd') 
left JOIN employee EM ON EM.EMPLOYEENAME = HML1.EMPLOYEENAME 
WHERE 1=1 AND hml1.SYSTEMDATE >=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')  AND hml1.SYSTEMDATE<= to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss')  
ORDER BY hml1.systemdate DESC 
)t where t.rno=1
)t1 left JOIN MESSCADA.DATA_GOKIN_WORKSHOP DGW ON DGW.MACHINENO = t1.MACHINENO
),
tab1 as(
select DGW.WORKSHOP||spec as WorkShop,dxType,dxTime,macNo,dbNo from (
   select substr(t1.jbnumber,6,3) as spec,
   (case when  t1.aramcode in ('L200') then '线网断线' 
    when t1.aramcode in ('G187','L201' )then '进线断线'
    when t1.aramcode in ('G188','L202')  then '收线断线' 
     when t1.aramcode ='G233'  then '线网断线' 
   end) dxType,t1.createtime dxTime
   ,t1.machineno macNo,t1.jbnumber dbNo,rank() over(partition by jbnumber order by t1.createtime) rk
   from MESSCADA.data_qpj_alam t1 
   where t1.jbnumber is not null and ((t1.machine_status=1 AND t1.AramCode='G233') OR t1.AramCode='L200') and t1.Cutting_depth>0  and t1.createtime>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss') and t1.createtime<=to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss')
   and not exists(
select 1 from MESSCADA.data_qpj_alam a1
where 
  (a1.AramCode in  ('L200','L201','L202') 
      or (a1.aramcode ='G233' and  a1.machine_status=1))
      and a1.createtime<to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')
      and a1.jbnumber=t1.jbnumber
     )   
   )t2 left JOIN MESSCADA.DATA_GOKIN_WORKSHOP DGW ON DGW.MACHINENO = t2.macNo
    where rk=1 
),
tab3 as(
select * from (
 select  
       c.containername as DH,
       DGW.WORKSHOP||substr(c.containername,6,3) as WorkShop,
       to_char(CUT.MOVEOUTTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')  AS XJSJ,
       hml1.resourcename as MachineNo,
       EMP.DESCRIPTION AS XJR
  from Container c
  inner join historymainline hml1
    on c.containername = hml1.containername
   and hml1.specname = N'切片'
    and hml1.cdoname = 'TrackInLot'
  inner join a_wiplothistory CUT
    on c.containerid = CUT.containerid
   and CUT.specname = N'切片'
  inner join employee EMP
    on EMP.employeename = CUT.Moveoutemployeename
  left JOIN MESSCADA.DATA_GOKIN_WORKSHOP DGW ON DGW.MACHINENO = hml1.resourcename
 where 1 = 1 
   AND cut.MOVEOUTTIMESTAMP>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss') AND cut.MOVEOUTTIMESTAMP<= to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss')
  GROUP BY 
          WorkShop,
          c.containername,
          cut.MOVEOUTTIMESTAMP,
          hml1.resourcename,
          CUT.MOVEOUTUSERNAME,
          EMP.DESCRIPTION
 ORDER BY cut.MOVEOUTTIMESTAMP desc  
)
) 
select b.workshop,0 as WP,
(select count(tab.JBNUMBER) from tab where tab.workshop=b.workshop) KJDS,
(select count(tab3.DH) from tab3 where tab3.workshop=b.workshop) XJDS,
(select count(DISTINCT tab.MACHINENO) from tab where tab.workshop=b.workshop) SBSL,
(select count(tab1.dbNo) from tab1 where tab1.workshop=b.workshop) DXDS 
 from tab b group by b.workshop 
)tb 
union all

select WORKSHOP,sum(tb1.KJDS),sum(tb1.XJDS),sum(tb1.DXDS),ROUND(sum(tb1.DXDS)*100/sum(tb1.KJDS),0)||'%' as DXRATE,sum(tb1.SBSL),ROUND(sum(tb1.KJDS)/sum(tb1.SBSL),1) as DJDS from
(
with tab as(
select DGW.WORKSHOP||t1.spec as WorkShop,to_char(t1.createtime,'yyyy-mm-dd hh24:mi:ss') stime,t1.createtime,t1.JBNUMBER,t1.OPRATION,t1.DESCRIPTION,t1.machineno,rank()over(partition by t1.machineno order by t1.createtime) rk from (
 select * from (
    select row_number() over (partition by c.containername order by hml1.systemdate) as rno, c.containername as JBNUMBER,substr(c.containername,6,3) as spec,hml1.systemdate AS createtime,
    hml1.resourcename as machineno,EM.DESCRIPTION as DESCRIPTION,em.employeename as OPRATION 
 from Container c 
left join historymainline hml1 on c.containername = hml1.containername and hml1.specname = N'切片' and hml1.cdoname = 'TrackInLot'
left join MESSCADA.data_shift t3 on to_char(hml1.systemdate,'YYYYMMdd') = to_char(t3.shift_time,'YYYYMMdd') 
left JOIN employee EM ON EM.EMPLOYEENAME = HML1.EMPLOYEENAME 
WHERE 1=1 AND hml1.SYSTEMDATE >=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')  AND hml1.SYSTEMDATE<=to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss')  
ORDER BY hml1.systemdate DESC 
)t where t.rno=1
)t1 left JOIN MESSCADA.DATA_GOKIN_WORKSHOP DGW ON DGW.MACHINENO = t1.MACHINENO
),
tab1 as(
select DGW.WORKSHOP||spec as WorkShop,dxType,dxTime,macNo,dbNo from (
   select substr(t1.jbnumber,6,3) as spec,
   (case when  t1.aramcode in ('L200') then '线网断线' 
    when t1.aramcode in ('G187','L201' )then '进线断线'
    when t1.aramcode in ('G188','L202')  then '收线断线' 
     when t1.aramcode ='G233'  then '线网断线' 
   end) dxType,t1.createtime dxTime
   ,t1.machineno macNo,t1.jbnumber dbNo,rank() over(partition by jbnumber order by t1.createtime) rk
   from MESSCADA.data_qpj_alam t1 
   where t1.jbnumber is not null and ((t1.machine_status=1 AND t1.AramCode='G233') OR t1.AramCode='L200') and t1.Cutting_depth>0  and t1.createtime>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss') and t1.createtime<=to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss')
   and not exists(
select 1 from MESSCADA.data_qpj_alam a1
where 
  (a1.AramCode in  ('L200','L201','L202') 
      or (a1.aramcode ='G233' and  a1.machine_status=1))
      and a1.createtime<to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss')
      and a1.jbnumber=t1.jbnumber
     )   
   )t2 left JOIN MESSCADA.DATA_GOKIN_WORKSHOP DGW ON DGW.MACHINENO = t2.macNo
    where rk=1 
),
tab3 as(
select * from (
 select  
       c.containername as DH,
       DGW.WORKSHOP||substr(c.containername,6,3) as WorkShop,
       to_char(CUT.MOVEOUTTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')  AS XJSJ,
       hml1.resourcename as MachineNo,
       EMP.DESCRIPTION AS XJR
  from Container c
  inner join historymainline hml1
    on c.containername = hml1.containername
   and hml1.specname = N'切片'
    and hml1.cdoname = 'TrackInLot'
  inner join a_wiplothistory CUT
    on c.containerid = CUT.containerid
   and CUT.specname = N'切片'
  inner join employee EMP
    on EMP.employeename = CUT.Moveoutemployeename
  left JOIN MESSCADA.DATA_GOKIN_WORKSHOP DGW ON DGW.MACHINENO = hml1.resourcename
 where 1 = 1 
   AND cut.MOVEOUTTIMESTAMP>=to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss') AND cut.MOVEOUTTIMESTAMP<= to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss')
  GROUP BY 
          WorkShop,
          c.containername,
          cut.MOVEOUTTIMESTAMP,
          hml1.resourcename,
          CUT.MOVEOUTUSERNAME,
          EMP.DESCRIPTION
 ORDER BY cut.MOVEOUTTIMESTAMP desc  
)
) 
select substr(b.workshop,0,3)||'合计' as workshop,0 as WP,
(select count(tab.JBNUMBER) from tab where tab.workshop=b.workshop) KJDS,
(select count(tab3.DH) from tab3 where tab3.workshop=b.workshop) XJDS,
(select count(DISTINCT tab.MACHINENO) from tab where tab.workshop=b.workshop) SBSL,
(select count(tab1.dbNo) from tab1 where tab1.workshop=b.workshop) DXDS 
 from tab b  group by WORKSHOP
)tb1 group by WORKSHOP order by WORKSHOP
";
            return base.Query(sql);
        }
        #endregion
    }
}
